# # ==========2题====================
# import sqlite3
# import random
#
# # 连接数据库
# conn = sqlite3.connect('students.db')
# cursor = conn.cursor()
#
# # 创建学生信息表
# cursor.execute('''
#     CREATE TABLE IF NOT EXISTS students (
#         id INTEGER PRIMARY KEY AUTOINCREMENT,
#         name TEXT,
#         age INTEGER,
#         address TEXT,
#         phone TEXT
#     )
# ''')
#
# def add_student(name, age, address, phone):
#     # 插入学生信息到数据库
#     cursor.execute('INSERT INTO students (name, age, address, phone) VALUES (?, ?, ?, ?)', (name, age, address, phone))
#     conn.commit()
#     print(f'{name}存入成功')
#
# def update_student(name, field, value):
#     # 更新学生信息
#     cursor.execute(f"UPDATE students SET {field} = ? WHERE name = ?", (value, name))
#     conn.commit()
#
# def delete_student(name):
#     # 删除学生信息
#     cursor.execute("DELETE FROM students WHERE name = ?", (name,))
#     conn.commit()
#
# def show_all_students():
#     # 显示全部学生姓名
#     cursor.execute("SELECT name FROM students")
#     results = cursor.fetchall()
#     for row in results:
#         print(row[0])
#
# def call_student():
#     # 随机抽取一个学生并进行点名
#     cursor.execute("SELECT name FROM students")
#     results = cursor.fetchall()
#     if results:
#         random_name = random.choice(results)[0]
#         print(f'{random_name}到！')
#     else:
#         print('没有学生信息')
#
# # 示例操作：
# add_student('刘闯', 88, '上海', '1834567890')
# add_student('秦彪', 99, '沈阳', '1823456789')
#
# show_all_students()
#
# update_student('刘闯', 'address', '新民')
#
# delete_student('秦彪')
#
# call_student()
#
# # 关闭数据库连接
# conn.close()
# ================3题=====
# import random
# import pymysql
#
# # 连接数据库
# connection = pymysql.connect(host='123.57.53.17',
#                              user='root',
#                              password='123456',
#                              database='xjglxt')
#
# # 生成班级号列表
# class_nums = ['1809A', '1809B', '1810A', '1810B']
#
# # 定义成绩段
# score_ranges = [
#     {'segment': '优秀', 'min': 90, 'max': 100},
#     {'segment': '良好', 'min': 80, 'max': 89},
#     {'segment': '中等', 'min': 70, 'max': 79},
#     {'segment': '及格', 'min': 60, 'max': 69},
#     {'segment': '不及格', 'min': 0, 'max': 59}
# ]
#
# # 随机生成学生信息，并插入数据库
# for _ in range(100):
#     student_id = random.randint(100000, 999999)
#     student_name = '学生' + str(student_id)
#     english_score = random.randint(0, 100)
#     math_score = random.randint(0, 100)
#     chinese_score = random.randint(0, 100)
#     class_num = random.choice(class_nums)
#
#     # 插入数据
#     with connection.cursor() as cursor:
#         sql = "INSERT INTO student_scores (student_id, student_name, english_score, math_score, chinese_score, class_num) VALUES (%s, %s, %s, %s, %s, %s)"
#         cursor.execute(sql, (student_id, student_name, english_score, math_score, chinese_score, class_num))
#
# # 提交事务并关闭连接
# connection.commit()
# connection.close()
# import pymysql
#
#
# class GradeManagementSystem:
#     def __init__(self):
#         self.connection = pymysql.connect(host='123.57.53.17',
#                                           user='root',
#                                           password='123456',
#                                           database='xjglxt')
#
#     def get_students_by_english_score(self, score_segment):
#         with self.connection.cursor() as cursor:
#             sql = "SELECT student_name, class_num FROM student_scores WHERE english_score >= %s AND english_score <= %s"
#             cursor.execute(sql, (score_segment['min'], score_segment['max']))
#             result = cursor.fetchall()
#         return result
#
#     def get_math_scores_by_class(self, class_nums):
#         math_scores = []
#         for class_num in class_nums:
#             with self.connection.cursor() as cursor:
#                 sql = "SELECT math_score FROM student_scores WHERE class_num = %s ORDER BY math_score DESC"
#                 cursor.execute(sql, class_num)
#                 scores = cursor.fetchall()
#                 math_scores.append((class_num, [score[0] for score in scores]))
#         return math_scores
#
#     def get_student_count_by_class(self, class_nums):
#         student_counts = []
#         for class_num in class_nums:
#             with self.connection.cursor() as cursor:
#                 sql = "SELECT COUNT(*) FROM student_scores WHERE class_num = %s"
#                 cursor.execute(sql, class_num)
#                 count = cursor.fetchone()[0]
#                 student_counts.append((class_num, count))
#
#         student_counts.sort(key=lambda x: x[1], reverse=True)  # 由大到小排序
#
#         return student_counts
#
#     def get_students_with_failing_grades(self):
#         with self.connection.cursor() as cursor:
#             sql = "SELECT student_name, class_num FROM student_scores WHERE english_score < 60 OR math_score < 60 OR chinese_score < 60 ORDER BY english_score DESC, math_score DESC, chinese_score DESC"
#             cursor.execute(sql)
#             result = cursor.fetchall()
#         return result
#
#     def close_connection(self):
#         self.connection.close()
#         # 创建成绩管理系统对象
# grade_sys = GradeManagementSystem()
#
# # 查询所有英语成绩为优秀的学生姓名和班级号
# excellent_students = grade_sys.get_students_by_english_score({'min': 90, 'max': 100})
# print("优秀成绩的学生：")
# for student in excellent_students:
#     print(student)
# excellent_students = grade_sys.get_students_by_english_score({'min': 80, 'max': 89})
# print("良好成绩的学生：")
# for student in excellent_students:
#     print(student)
# excellent_students = grade_sys.get_students_by_english_score({'min': 70, 'max': 79})
# print("中等成绩的学生：")
# for student in excellent_students:
#     print(student)
# excellent_students = grade_sys.get_students_by_english_score({'min': 60, 'max': 69})
# print("合格成绩的学生：")
# for student in excellent_students:
#     print(student)
# excellent_students = grade_sys.get_students_by_english_score({'min': 0, 'max': 59})
# print("不合格成绩的学生：")
# for student in excellent_students:
#     print(student)
#
# # 查询1809A，1809B，1810A，1810B每个班级的数学成绩，并且按降序返回
# math_scores_by_class = grade_sys.get_math_scores_by_class(['1809A', '1809B', '1810A', '1810B'])
# print("各班级数学成绩（降序）：")
# for class_num, scores in math_scores_by_class:
#     print(class_num, scores)
#
# # 查询1809A，1809B，1810A，1810B每个班级的总人数，并且由大到小排序返回
# student_counts_by_class = grade_sys.get_student_count_by_class(['1809A', '1809B', '1810A', '1810B'])
# print("各班级总人数（由大到小）：")
# for class_num, count in student_counts_by_class:
#     print(class_num, count)
#
# # 查询英语、数学、语文成绩不合格的学生姓名和对应的班级号，按照不合格的人数，由大到小排序
# failing_students = grade_sys.get_students_with_failing_grades()
# print("不合格成绩的学生：")
# for student in failing_students:
#     print(student)
#
# # 关闭数据库连接
# grade_sys.close_connection()
import pymysql

class BookManagementSystem:
    def __init__(self):
        self.connection = pymysql.connect(host='123.57.53.17',
                                          user='root',
                                          password='123456',
                                          database='books')

    def add_book(self, book_name, author_name, create_date):
        with self.connection.cursor() as cursor:
            sql = "INSERT INTO books (book_name, author_name, create_date) VALUES (%s, %s, %s)"
            cursor.execute(sql, (book_name, author_name, create_date))
        self.connection.commit()

    def update_book(self, book_id, book_name, author_name, create_date):
        with self.connection.cursor() as cursor:
            sql = "UPDATE books SET book_name = %s, author_name = %s, create_date = %s WHERE book_id = %s"
            cursor.execute(sql, (book_name, author_name, create_date, book_id))
        self.connection.commit()

    def delete_book(self, book_id):
        with self.connection.cursor() as cursor:
            sql = "DELETE FROM books WHERE book_id = %s"
            cursor.execute(sql, book_id)
        self.connection.commit()

    def get_all_books(self):
        with self.connection.cursor() as cursor:
            sql = "SELECT * FROM books"
            cursor.execute(sql)
            result = cursor.fetchall()
        return result

    def get_book_by_exact_name(self, book_name):
        with self.connection.cursor() as cursor:
            sql = "SELECT * FROM books WHERE book_name = %s"
            cursor.execute(sql, book_name)
            result = cursor.fetchall()
        return result

    def get_books_by_partial_name(self, partial_name):
        with self.connection.cursor() as cursor:
            sql = "SELECT * FROM books WHERE book_name LIKE %s"
            cursor.execute(sql, f"%{partial_name}%")
            result = cursor.fetchall()
        return result

    def close_connection(self):
        self.connection.close()
# 创建图书管理系统对象
book_sys = BookManagementSystem()

# 新增图书
book_sys.add_book("Book 1", "Author 1", "2023-08-28")
book_sys.add_book("Book 2", "Author 2", "2023-08-29")

# 修改图书
book_sys.update_book(1, "Updated Book 1", "Updated Author 1", "2023-08-30")

# 删除图书
book_sys.delete_book(2)

# 查询所有图书
all_books = book_sys.get_all_books()
print(all_books)

# 精确查询图书
exact_books = book_sys.get_book_by_exact_name("Updated Book 1")
print(exact_books)

# 模糊查询图书
partial_books = book_sys.get_books_by_partial_name("Book")
print(partial_books)

# 关闭数据库连接
book_sys.close_connection()